﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CROSLAND.BL.BE;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace CROSLAND.DL.DALC.MSSQL
{
    public class ClienteDALC : BaseDALC, IClienteDALC
    {
        String stCon = ConfigurationManager.ConnectionStrings["MSSQLCon"].ToString();
        SqlConnection cn = null;
        SqlCommand cmd = null;
        SqlParameter prm = null;

        public List<InterlocutorBE> buscaInterlocutores(string pCodCliente, string pCodOrgVtas, string pCodCanalDist, string pSector)
        {
            List<InterlocutorBE> listaResult = new List<InterlocutorBE>();
            InterlocutorBE objInterlocutor = null; ClienteBE obj = null; PersonaBE objPer = null; string strCodPersona = string.Empty;
            DbCommand command = dbProvider.GetStoredProcCommand("USPR_BUSCA_INTERLOCUTORES");
            dbProvider.AddInParameter(command, "KUNNR", DbType.String, pCodCliente);
            dbProvider.AddInParameter(command, "VKORG", DbType.String, pCodOrgVtas);
            dbProvider.AddInParameter(command, "VTWEG", DbType.String, pCodCanalDist);
            dbProvider.AddInParameter(command, "SPART", DbType.String, pSector);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    obj = new ClienteBE();
                    obj.Codigo = dr["KUNNR"].ToString();
                    obj.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.Ruc = dr["STCD1"].ToString();
                    obj.NOMBRE = dr["NAME1"].ToString().Trim();
                    obj.APPAT = (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.tipoNif = dr["STCDT"].ToString();
                    obj.nif = dr["STCD1"].ToString();
                    obj.Direccion = dr["STRAS"].ToString();
                    obj.Distrito = dr["ORT02"].ToString();
                    obj.Ciudad = dr["ORT01"].ToString();
                    obj.poblacion = dr["ORT01"].ToString().TrimStart('0');
                    obj.region = dr["REGIO"].ToString();
                    obj.Telefono = dr["TELF1"].ToString();
                    obj.zonaRegistral = dr["KUKLA"].ToString();
                    obj.grupoCliente = dr["KVGR1"].ToString();
                    obj.moneda = dr["WAERS"].ToString();
                    obj.oficinaVentas = dr["VKBUR"].ToString();
                    obj.Mail = dr["SMTP_ADDR"].ToString();
                    if (dr["XCPDK"].ToString().Equals("X")) { obj.Eventual = true; } else { obj.Eventual = false; }
                    obj.vend_code = dr["VEND_CODE"].ToString();
                    obj.vend_text = dr["VEND_TEXT"].ToString();
                    obj.cond_pago = dr["ZTERM"].ToString();

                    objInterlocutor = new InterlocutorBE();
                    strCodPersona = obj.vend_code.Trim();
                    if (string.IsNullOrEmpty(strCodPersona) || strCodPersona.Equals("00000000"))
                    {
                        objInterlocutor.Cliente = obj;
                    }
                    else
                    {
                        objPer = new PersonaBE();
                        objPer.CodPersona = obj.vend_code;
                        objPer.Descripcion = obj.vend_text;
                        objInterlocutor.Persona = objPer;
                    }
                    objInterlocutor.Funcion = dr["PARVW"].ToString();

                    listaResult.Add(objInterlocutor);
                }
            }

            return listaResult;
        }

        #region IClienteDALC Members

        //No usar para NCO3 se modifico.
        /// <summary>
        /// Devuelve una lista de clientes segun el parametro.
        /// </summary>
        /// <param name="pNombre">Nombre o parte del nombre de un cliente.</param>
        /// <returns>Devuelve lista de clientes</returns> 
        public List<ClienteBE> buscar(string pNombre)
        {
            List<ClienteBE> entitys = new List<ClienteBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_BUSCA_CLIENTES");
            dbProvider.AddInParameter(command, "IM_NAME", DbType.String, pNombre);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    ClienteBE entity = new ClienteBE();

                    entity.Codigo = dr["KUNNR"].ToString();
                    entity.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? ""
                        : " " + dr["NAME2"].ToString());
                    entity.Ruc = dr["STCD1"].ToString();

                    entitys.Add(entity);
                }
            }

            return entitys;
        }

        /// <summary>
        /// retorna la información del cliente
        /// </summary>
        /// <param name="pCodigo">Código del cliente</param>
        /// <returns>Devuelve los datos del cliente</returns>
        public ClienteBE leer(string pCodigo)
        {
            ClienteBE entity = new ClienteBE();

            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_LEE_CLIENTE");
            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pCodigo);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    entity.Codigo = dr["KUNNR"].ToString();
                    entity.Ruc = dr["STCD1"].ToString();
                    entity.Descripcion = dr["NOMBRE"].ToString();
                    entity.Ciudad = dr["CITY"].ToString();
                    entity.Direccion = dr["STREET"].ToString();
                    entity.CodigoPostal = dr["POSTL_CODE"].ToString();
                    entity.Telefono = dr["TELEPHONE"].ToString();
                    entity.Mail = dr["E_MAIL"].ToString();

                    string CLIEVE = dr["EX_CLIEVE"].ToString();

                    entity.Eventual = CLIEVE == "X" ? true : false;
                }
            }

            return entity;
        }

        /// <summary>
        /// devuelve la lista de organizaciones de venta
        /// </summary>
        /// <param name="pCodCliente">Código del cliente</param>
        /// <returns>Lista de organizaciones de venta</returns>
        public List<GenericBE> listaOrgVtas(string pCodCliente)
        {
            List<GenericBE> entitys = new List<GenericBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_LISTA_ORGVTAS");
            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pCodCliente);


            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    GenericBE entity = new GenericBE();

                    entity.Codigo = dr["VKORG"].ToString();
                    entity.Descripcion = dr["VKORG"].ToString() + " :: " + dr["VTEXT"].ToString();

                    entitys.Add(entity);
                }
            }

            return entitys;
        }

        public List<GenericBE> listaCanalDist(string pCodCliente, string pCodOrgVtas)
        {
            List<GenericBE> entitys = new List<GenericBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_LISTA_CANALDIST");
            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pCodCliente);
            dbProvider.AddInParameter(command, "IM_VKORG", DbType.String, pCodOrgVtas);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    GenericBE entity = new GenericBE();

                    entity.Codigo = dr["VTWEG"].ToString();
                    entity.Descripcion = dr["VTWEG"].ToString() + " :: " + dr["VTEXT"].ToString();

                    entitys.Add(entity);
                }
            }

            return entitys;
        }

        public List<GenericBE> listaSectores()
        {
            throw new NotImplementedException();
        }

        public PersonaBE buscaVendedor(string pCodCliente, string pCodOrgVtas, string pCodCanalDist)
        {
            PersonaBE entity = new PersonaBE();

            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_BUSCA_VENDEDOR");
            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pCodCliente);
            dbProvider.AddInParameter(command, "IM_VKORG", DbType.String, pCodOrgVtas);
            dbProvider.AddInParameter(command, "IM_VTWEG", DbType.String, pCodCanalDist);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    entity.CodPersona = dr["PERNR"].ToString();
                    entity.Nombre = dr["VORNA"].ToString();
                    entity.Apellido = dr["NACHN"].ToString();
                    entity.ApeSoltero = dr["NAME2"].ToString();
                    entity.Iniciales = dr["INITS"].ToString();
                    entity.Descripcion = (dr["VORNA"].ToString() + " " + dr["NACHN"].ToString() + " " + dr["NAME2"].ToString()).Trim();
                }
            }

            return entity;
        }

        /// <summary>
        /// Busca Condicion Pago y Expedicion
        /// </summary>
        /// <param name="pCodCliente">Código de cliente.</param>
        /// <param name="pCodOrgVtas">Código de Organización de ventas.</param>
        /// <param name="pCodCanalDist">Código de Canal de distribución.</param>
        /// <param name="pCodSector">Código de sector.</param>
        /// <param name="pCondPago">Valor de retorno: Clave de condiciones de pago.</param>
        /// <param name="pCondExp">Valor de retorno: Condición de expedición.</param>  
        public void infoClienteNac(string pCodCliente, string pCodOrgVtas, string pCodCanalDist, string pCodSector,
            out string pCondPago, out string pCondExp)
        {
            DbCommand command = dbProvider.GetStoredProcCommand("ZWEB_BUSCA_CONDICIONES");

            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pCodCliente);
            dbProvider.AddInParameter(command, "IM_VKORG", DbType.String, pCodOrgVtas);
            dbProvider.AddInParameter(command, "IM_VTWEG", DbType.String, pCodCanalDist);
            dbProvider.AddInParameter(command, "IM_SPART", DbType.String, pCodSector);


            pCondExp = ""; pCondPago = "";

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    pCondExp = dr.GetString(dr.GetOrdinal("EX_VSBED"));
                    pCondPago = dr.GetString(dr.GetOrdinal("EX_ZTERM"));
                }
            }
        }

        public string validaInterlocutor(ref ClienteBE pClienteBE, string pCodOrgVtas, string pCodCanalDist, string pCodSector)
        {
            throw new NotImplementedException();
        }

        public string validaRUC(string pRuc, string pOrgVtas)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IClienteDALC Members

        /// <summary>
        /// Devuelve una lista de clientes segun los parámetros.
        /// </summary>
        /// <param name="pNombre">Nombre o parte del nombre de un cliente.</param>
        /// <param name="pIdentifFiscal">Número de identificación fiscal.</param>
        /// <param name="pNumeroDeudor">Número de deudor.</param>
        /// <returns>Devuelve lista de clientes</returns> 
        public List<ClienteBE> busquedaCliente(string pNombre, string pIdentifFiscal, string pNumeroDeudor)
        {
            List<ClienteBE> entitys = new List<ClienteBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("ZCM_SD_006");
            dbProvider.AddInParameter(command, "IM_NAME", DbType.String, pNombre);
            dbProvider.AddInParameter(command, "IM_STCD1", DbType.String, pIdentifFiscal);
            dbProvider.AddInParameter(command, "IM_KUNNR", DbType.String, pNumeroDeudor);

            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    ClienteBE entity = new ClienteBE();

                    entity.Codigo = dr["KUNNR"].ToString();
                    entity.Descripcion = dr["NOMBRE"].ToString().Trim();
                    entity.Ruc = dr["STCD1"].ToString();

                    entitys.Add(entity);
                }
            }

            return entitys;
        }
        
        #endregion

        
        public List<ClienteBE> buscarCliente(string pCodigo, string pNif, string pNombre)
        {
            throw new NotImplementedException();
        }

        public List<ClienteBE> buscarEntFin(string pCodigo, string pNombre, UsuarioWebBE uw)
        {
            List<ClienteBE> entitys = new List<ClienteBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("USPR_BUSCAR_ENTIDADFINANCIERA");
            dbProvider.AddInParameter(command, "I_KUNNR", DbType.String, pCodigo);
            dbProvider.AddInParameter(command, "I_STCD1", DbType.String, "");
            dbProvider.AddInParameter(command, "I_NAME1", DbType.String, pNombre);
            dbProvider.AddInParameter(command, "I_COUNT", DbType.Int32, 50);
            dbProvider.AddInParameter(command, "I_ORGVTA", DbType.String, uw.OrgVentas);
            dbProvider.AddInParameter(command, "I_CANAL", DbType.String, uw.CanalDist);
            dbProvider.AddInParameter(command, "I_SECTOR", DbType.String, uw.sector);
            //DataTable dt = new DataTable();
            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    ClienteBE obj = new ClienteBE();
                    obj.Codigo = dr["KUNNR"].ToString();
                    obj.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.Ruc = dr["STCD1"].ToString();
                    obj.NOMBRE = dr["NAME1"].ToString().Trim();
                    obj.APPAT = (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.tipoNif = dr["STCDT"].ToString();
                    obj.nif = dr["STCD1"].ToString();
                    obj.Direccion = dr["STRAS"].ToString();
                    obj.Distrito = dr["ORT02"].ToString();
                    obj.Ciudad = dr["ORT01"].ToString();
                    obj.poblacion = dr["ORT01"].ToString().TrimStart('0');
                    obj.region = dr["REGIO"].ToString();
                    obj.Telefono = dr["TELF1"].ToString();
                    obj.zonaRegistral = dr["KUKLA"].ToString();
                    obj.grupoCliente = dr["KVGR1"].ToString();
                    obj.moneda = dr["WAERS"].ToString();
                    obj.oficinaVentas = dr["VKBUR"].ToString();
                    obj.Mail = dr["SMTP_ADDR"].ToString();
                    if (dr["XCPDK"].ToString().Equals("X")) { obj.Eventual = true; } else { obj.Eventual = false; }
                    //obj.vend_code = dr["VEND_CODE"].ToString();
                    obj.vend_text = dr["VEND_TEXT"].ToString();
                    obj.cond_pago = dr["ZTERM"].ToString();

                    entitys.Add(obj);
                }
                //dt.Load(dr);
                if (entitys.Count == 0)
                {
                    command = dbProvider.GetStoredProcCommand("USPR_BUSCAR_ENTIDADFINANCIERA");
                    dbProvider.AddInParameter(command, "I_KUNNR", DbType.String, "");
                    dbProvider.AddInParameter(command, "I_STCD1", DbType.String, pCodigo);
                    dbProvider.AddInParameter(command, "I_NAME1", DbType.String, pNombre);
                    dbProvider.AddInParameter(command, "I_COUNT", DbType.Int32, 50);
                    dbProvider.AddInParameter(command, "I_ORGVTA", DbType.String, uw.OrgVentas);
                    dbProvider.AddInParameter(command, "I_CANAL", DbType.String, uw.CanalDist);
                    dbProvider.AddInParameter(command, "I_SECTOR", DbType.String, uw.sector);

                    using (IDataReader dr1 = dbProvider.ExecuteReader(command))
                    {
                        while (dr1.Read())
                        {
                            ClienteBE obj = new ClienteBE();
                            obj.Codigo = dr["KUNNR"].ToString();
                            obj.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                            obj.Ruc = dr["STCD1"].ToString();
                            obj.NOMBRE = dr["NAME1"].ToString().Trim();
                            obj.APPAT = (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                            obj.tipoNif = dr["STCDT"].ToString();
                            obj.nif = dr["STCD1"].ToString();
                            obj.Direccion = dr["STRAS"].ToString();
                            obj.Distrito = dr["ORT02"].ToString();
                            obj.Ciudad = dr["ORT01"].ToString();
                            obj.poblacion = dr["ORT01"].ToString().TrimStart('0');
                            obj.region = dr["REGIO"].ToString();
                            obj.Telefono = dr["TELF1"].ToString();
                            obj.zonaRegistral = dr["KUKLA"].ToString();
                            obj.grupoCliente = dr["KVGR1"].ToString();
                            obj.moneda = dr["WAERS"].ToString();
                            obj.oficinaVentas = dr["VKBUR"].ToString();
                            obj.Mail = dr["SMTP_ADDR"].ToString();
                            if (dr["XCPDK"].ToString().Equals("X")) { obj.Eventual = true; } else { obj.Eventual = false; }
                            //obj.vend_code = dr["VEND_CODE"].ToString();
                            obj.vend_text = dr["VEND_TEXT"].ToString();
                            obj.cond_pago = dr["ZTERM"].ToString();

                            entitys.Add(obj);
                        }
                    }
                }
            }

            return entitys;
        }

        
        public string creaCliente(ClienteBE objCli, UsuarioWebBE usrWeb, out string codCli)
        {
            DataTable dtKnvp = new DataTable("P_T_KNVP");
            dtKnvp.Columns.Add("P_PARVW", typeof(string));
            dtKnvp.Columns.Add("P_PARZA", typeof(string));
            dtKnvp.Columns.Add("P_KUNN2", typeof(string));
            dtKnvp.Columns.Add("P_PSTLZ", typeof(string));
            string result = "";
            cn = new SqlConnection(stCon);
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
           

            SqlCommand cmd = new SqlCommand("USPC_CREAR_CLIENTE", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            prm = new SqlParameter("P_STCD1", SqlDbType.NVarChar);
            prm.Value = objCli.nif;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_VKORG", SqlDbType.NVarChar);
            prm.Value = usrWeb.OrgVentas;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_VTWEG", SqlDbType.NVarChar);
            prm.Value = usrWeb.CanalDist;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_SPART", SqlDbType.NVarChar);
            prm.Value = usrWeb.sector;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_KTOKD", SqlDbType.NVarChar);
            prm.Value = "ZCLI";
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);
            
            prm = new SqlParameter("P_NAME1", SqlDbType.NVarChar);
            prm.Value = objCli.NOMBRE;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_NAME2", SqlDbType.NVarChar);
            prm.Value = objCli.APPAT;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_EMAIL", SqlDbType.NVarChar);
            prm.Value = objCli.Mail;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_STRAS", SqlDbType.NVarChar);
            prm.Value = objCli.Direccion;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_ORT01", SqlDbType.NVarChar);
            prm.Value = objCli.poblacion;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_ORT02", SqlDbType.NVarChar);
            prm.Value = objCli.Distrito;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_REGIO", SqlDbType.NVarChar);
            prm.Value = objCli.region;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_TELF1", SqlDbType.NVarChar);
            prm.Value = objCli.Telefono;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            //prm = new SqlParameter("P_KNURL", SqlDbType.NVarChar);
            //prm.Value = objCli.Mail;
            //prm.Direction = ParameterDirection.Input;
            //cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_STCDT", SqlDbType.NVarChar);
            prm.Value = objCli.tipoNif;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_KUKLA", SqlDbType.NVarChar);
            prm.Value = objCli.zonaRegistral;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_WAERS", SqlDbType.NVarChar);
            prm.Value = objCli.moneda;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_KVGR1", SqlDbType.NVarChar);
            prm.Value = objCli.grupoCliente;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_PERNR", SqlDbType.NVarChar);
            prm.Value = usrWeb.PerNr;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_VKBUR", SqlDbType.NVarChar);
            prm.Value = usrWeb.OfVentas;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("UPDKZ", SqlDbType.NVarChar);
            prm.Value = "I";
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_DISTRICT", SqlDbType.NVarChar);
            prm.Value = objCli.Distrito;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("VENDEDOR", SqlDbType.NVarChar);
            prm.Value = usrWeb.PerNr;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            dtKnvp.Rows.Add("AG", "", "", "");
            dtKnvp.Rows.Add("RE", "", "", "");
            dtKnvp.Rows.Add("RG", "", "", "");
            dtKnvp.Rows.Add("WE", "", "", "");

            prm = new SqlParameter("P_T_KNVP", SqlDbType.Structured);
            prm.Value = dtKnvp;
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_KDGRP", SqlDbType.NVarChar);
            prm.Value = "";
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_KALKS", SqlDbType.NVarChar);
            prm.Value = "1";
            prm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(prm);

            prm = new SqlParameter("P_RESULT", SqlDbType.NVarChar);
            prm.Value = "0";
            prm.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(prm);

            codCli = cmd.ExecuteScalar().ToString();

            result = cmd.Parameters["P_RESULT"].Value.ToString();

            if (result == "0")
            {
                result = "El NIF ingresado ya existe";
            }
            return result;
        }



        public string modificaCliente(ClienteBE objCli, UsuarioWebBE usrWeb)
        {
            cn = new SqlConnection(stCon);
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            DbCommand command = dbProvider.GetStoredProcCommand("USPU_MODIFICA_CLIENTE");
            command.Connection = cn;
            dbProvider.AddInParameter(command, "P_KUNNR", DbType.String, objCli.Codigo);
            dbProvider.AddInParameter(command, "P_STCD1", DbType.String, objCli.nif);
            dbProvider.AddInParameter(command, "P_VKORG", DbType.String, usrWeb.OrgVentas);
            dbProvider.AddInParameter(command, "P_VTWEG", DbType.String, usrWeb.CanalDist);
            dbProvider.AddInParameter(command, "P_SPART", DbType.String, usrWeb.sector);

            dbProvider.AddInParameter(command, "P_KTOKD", DbType.String, "ZCLI");
            dbProvider.AddInParameter(command, "P_NAME1", DbType.String, objCli.NOMBRE);
            dbProvider.AddInParameter(command, "P_NAME2", DbType.String, objCli.APPAT);
            dbProvider.AddInParameter(command, "P_STRAS", DbType.String, objCli.Direccion);
            dbProvider.AddInParameter(command, "P_ORT01", DbType.String, objCli.poblacion);
            dbProvider.AddInParameter(command, "P_ORT02", DbType.String, objCli.Distrito);
            dbProvider.AddInParameter(command, "P_REGIO", DbType.String, objCli.region);
            dbProvider.AddInParameter(command, "P_TELF1", DbType.String, objCli.Telefono);
            dbProvider.AddInParameter(command, "P_KNURL", DbType.String, objCli.Mail);            
            dbProvider.AddInParameter(command, "P_STCDT", DbType.String, objCli.tipoNif);
            dbProvider.AddInParameter(command, "P_KUKLA", DbType.String, objCli.zonaRegistral);
            dbProvider.AddInParameter(command, "P_PERNR", DbType.String, usrWeb.PerNr);
            dbProvider.AddInParameter(command, "P_DISTRICT", DbType.String, objCli.Distrito);
            dbProvider.AddInParameter(command, "UPDKZ", DbType.String, "U");
            dbProvider.AddInParameter(command, "KVGR1", DbType.String, objCli.grupoCliente);
            dbProvider.AddInParameter(command, "P_EMAIL", DbType.String, objCli.Mail);

            int i = Convert.ToInt16(command.ExecuteScalar());
            string falta = "Se modificó el cliente";
            return falta;
        }

        public List<GenericBE> buscaVendedor(string txtVen, string codVen, string ofVta, DateTime fecha)
        {
            throw new NotImplementedException();
        }

        public List<ClienteBE.BancoBE> listaBanco(string codCliente, string soc)
        {
            List<ClienteBE.BancoBE> lstResutl = new List<ClienteBE.BancoBE>();
            ClienteBE.BancoBE banco;

            DbCommand command = dbProvider.GetStoredProcCommand("USPR_BUSCAR_CREDIBANCO");
            dbProvider.AddInParameter(command, "COD_CLIENTE", DbType.String, codCliente);
            dbProvider.AddInParameter(command, "SOCIEDAD", DbType.String, soc);
            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    banco = new ClienteBE.BancoBE();
                    banco.codCliente = codCliente;
                    banco.banco = dr["COD_BANCO"].ToString().Trim();
                    banco.txtBanco = dr["DESC_LARGA_BANCO"].ToString().Trim();
                    banco.credipago = dr["CREDIPAGO"].ToString().Trim();
                    lstResutl.Add(banco);
                }
            }
            return lstResutl;
        }

        #region Miembros de IClienteDALC

        public List<ClienteBE> buscar(string pNombre, UsuarioWebBE uw)
        {
            throw new NotImplementedException();
        }

        public ClienteBE leer(string pCodigo, UsuarioWebBE uw)
        {
            throw new NotImplementedException();
        }

        public List<ClienteBE> buscarCliente(string pCodigo, string pNombre, UsuarioWebBE uw)
        {
            List<ClienteBE> entitys = new List<ClienteBE>();

            DbCommand command = dbProvider.GetStoredProcCommand("USPR_BUSCAR_CLIENTE");
            dbProvider.AddInParameter(command, "I_KUNNR", DbType.String, pCodigo);
            dbProvider.AddInParameter(command, "I_STCD1", DbType.String, "");
            dbProvider.AddInParameter(command, "I_NAME1", DbType.String, pNombre);
            dbProvider.AddInParameter(command, "I_COUNT", DbType.Int32, 50);
            dbProvider.AddInParameter(command, "I_ORGVTA", DbType.String, uw.OrgVentas);
            dbProvider.AddInParameter(command, "I_CANAL", DbType.String, uw.CanalDist);
            dbProvider.AddInParameter(command, "I_SECTOR", DbType.String, uw.sector);
            //DataTable dt = new DataTable();
            using (IDataReader dr = dbProvider.ExecuteReader(command))
            {   
                while (dr.Read())
                {
                    ClienteBE obj = new ClienteBE();
                    obj.Codigo = dr["KUNNR"].ToString();
                    obj.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.Ruc = dr["STCD1"].ToString();
                    obj.NOMBRE = dr["NAME1"].ToString().Trim();
                    obj.APPAT = (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                    obj.tipoNif = dr["STCDT"].ToString();
                    obj.nif = dr["STCD1"].ToString();
                    obj.Direccion = dr["STRAS"].ToString();
                    obj.Distrito = dr["ORT02"].ToString();
                    obj.Ciudad = dr["ORT01"].ToString();
                    obj.poblacion = dr["ORT01"].ToString().TrimStart('0');
                    obj.region = dr["REGIO"].ToString();
                    obj.Telefono = dr["TELF1"].ToString();
                    obj.zonaRegistral = dr["KUKLA"].ToString();
                    obj.grupoCliente = dr["KVGR1"].ToString();
                    obj.moneda = dr["WAERS"].ToString();
                    obj.oficinaVentas = dr["VKBUR"].ToString();
                    obj.Mail = dr["SMTP_ADDR"].ToString();
                    if (dr["XCPDK"].ToString().Equals("X")) { obj.Eventual = true; } else { obj.Eventual = false; }
                    //obj.vend_code = dr["VEND_CODE"].ToString();
                    obj.vend_text = dr["VEND_TEXT"].ToString();
                    obj.cond_pago = dr["ZTERM"].ToString();
                    obj.ISC = dr["ISC"].ToString();
                    obj.IGV = dr["IGV"].ToString();

                    entitys.Add(obj);
                }
                //dt.Load(dr);
                if (entitys.Count == 0)
                {
                    command = dbProvider.GetStoredProcCommand("USPR_BUSCAR_CLIENTE");
                    dbProvider.AddInParameter(command, "I_KUNNR", DbType.String, "");
                    dbProvider.AddInParameter(command, "I_STCD1", DbType.String, pCodigo);
                    dbProvider.AddInParameter(command, "I_NAME1", DbType.String, pNombre);
                    dbProvider.AddInParameter(command, "I_COUNT", DbType.Int32, 50);
                    dbProvider.AddInParameter(command, "I_ORGVTA", DbType.String, uw.OrgVentas);
                    dbProvider.AddInParameter(command, "I_CANAL", DbType.String, uw.CanalDist);
                    dbProvider.AddInParameter(command, "I_SECTOR", DbType.String, uw.sector);

                    using (IDataReader dr1 = dbProvider.ExecuteReader(command))
                    {
                        while (dr1.Read())
                        {
                            ClienteBE obj = new ClienteBE();
                            obj.Codigo = dr["KUNNR"].ToString();
                            obj.Descripcion = dr["NAME1"].ToString().Trim() + (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                            obj.Ruc = dr["STCD1"].ToString();
                            obj.NOMBRE = dr["NAME1"].ToString().Trim();
                            obj.APPAT = (dr["NAME2"].ToString().Trim().Equals("") ? "" : " " + dr["NAME2"].ToString());
                            obj.tipoNif = dr["STCDT"].ToString();
                            obj.nif = dr["STCD1"].ToString();
                            obj.Direccion = dr["STRAS"].ToString();
                            obj.Distrito = dr["ORT02"].ToString();
                            obj.Ciudad = dr["ORT01"].ToString();
                            obj.poblacion = dr["ORT01"].ToString().TrimStart('0');
                            obj.region = dr["REGIO"].ToString();
                            obj.Telefono = dr["TELF1"].ToString();
                            obj.zonaRegistral = dr["KUKLA"].ToString();
                            obj.grupoCliente = dr["KVGR1"].ToString();
                            obj.moneda = dr["WAERS"].ToString();
                            obj.oficinaVentas = dr["VKBUR"].ToString();
                            obj.Mail = dr["SMTP_ADDR"].ToString();
                            if (dr["XCPDK"].ToString().Equals("X")) { obj.Eventual = true; } else { obj.Eventual = false; }
                            //obj.vend_code = dr["VEND_CODE"].ToString();
                            obj.vend_text = dr["VEND_TEXT"].ToString();
                            obj.cond_pago = dr["ZTERM"].ToString();
                            obj.ISC = dr["ISC"].ToString();
                            obj.IGV = dr["IGV"].ToString();

                            entitys.Add(obj);
                        }
                    }
                }
            }

            return entitys;
        }

        #endregion
    }
}
